package com.singfan.common.db;

import com.singfan.common.SingFanException;
import com.singfan.common.util.StringUtil;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

/**
 * 数据库访问基类
 * Author: HanSongHe
 * Date: 15-7-12 上午11:34
 */
public abstract class DaoBase {
    protected abstract String getDatabaseId(Object hashKey);

    /**
     * 执行更新操作的sql语句
     * @param hashKey hashKey
     * @param sql sql语句
     * @param paramList 参数列表
     */
    protected void execute(Object hashKey, String sql, List<Object> paramList) {
        String databaseId = getDatabaseId(hashKey);
        if (!StringUtil.isNullOrEmpty(databaseId)){
            boolean write = DbContext.getConnectionPoolProvider().write(databaseId);
            if (write){
                Connection connection = null;
                try {
                    connection = getConnection(hashKey);
                    PreparedStatement preparedStatement = createPreparedStatement(connection, sql, paramList);

                    long executeBeginTime = System.currentTimeMillis();
                    preparedStatement.execute();
                    long executeUsedTime = System.currentTimeMillis() - executeBeginTime;
                    DbLogger.executeSqlLog(executeUsedTime, hashKey, sql, DbUtil.getSqlParamInfo(paramList));

                    preparedStatement.close();
                }
                catch (Exception ex){
                    DbLogger.executeSqlErrorLog(hashKey, sql, DbUtil.getSqlParamInfo(paramList), ex);
                    throw new SingFanException(String.format("DaoBase.execute() error: %1$s hashKey(%2$s) sql(%3$s) params(%4$s) !", ex.getMessage(), hashKey, sql, DbUtil.getSqlParamInfo(paramList)), ex);
                }
                finally {
                    closeConnection(connection);
                }
            }
            else {
                throw new SingFanException(String.format("DaoBase.execute() error: databaseId(%s) can not write !", databaseId));
            }
        }
        else {
            throw new SingFanException(String.format("DaoBase.execute() error: can not get databaseId from hashKey(%s)",hashKey));
        }
    }

    /**
     * 执行更新操作的sql语句
     * @param hashKey hashKey
     * @param sql sql语句
     * @param params 参数列表
     */
    protected void execute(Object hashKey, String sql, Object... params) {
        List<Object> paramList = DbUtil.params2paramList(params);
        execute(hashKey, sql, paramList);
    }

    /**
     * 执行更新操作的sql语句
     * @param hashKey hashKey
     * @param sql sql语句
     */
    protected void execute(Object hashKey, String sql) {
        execute(hashKey, sql, new ArrayList<>());
    }

    /**
     * 执行更新操作的sql语句，并返回结果
     * @param hashKey hashKey
     * @param sql sql语句
     * @param paramList 参数列表
     * @return 执行结果
     */
    protected Object executeWithResult(Object hashKey, String sql, List<Object> paramList, IStatementProcessor iStatementProcessor){
        String databaseId = getDatabaseId(hashKey);
        if (!StringUtil.isNullOrEmpty(databaseId)){
            boolean write = DbContext.getConnectionPoolProvider().write(databaseId);
            if (write){
                Connection connection = null;
                try {
                    Object result;
                    connection = getConnection(hashKey);
                    PreparedStatement preparedStatement = createPreparedStatementWithReturnGeneratedKeys(connection, sql, paramList);
                    result = preparedStatement.executeUpdate();

                    long executeBeginTime = System.currentTimeMillis();
                    if(iStatementProcessor != null){
                        result = iStatementProcessor.afterExecute(preparedStatement);
                    }
                    long executeUsedTime = System.currentTimeMillis() - executeBeginTime;
                    DbLogger.executeSqlLog(executeUsedTime, hashKey, sql, DbUtil.getSqlParamInfo(paramList));

                    preparedStatement.close();

                    return result;
                }
                catch (Exception ex){
                    DbLogger.executeSqlErrorLog(hashKey, sql, DbUtil.getSqlParamInfo(paramList), ex);
                    throw new SingFanException(String.format("DaoBase.executeWithResult() error: %1$s hashKey(%2$s) sql(%3$s) params(%4$s) !", ex.getMessage(), hashKey, sql, DbUtil.getSqlParamInfo(paramList)), ex);
                }
                finally {
                    closeConnection(connection);
                }
            }
            else {
                throw new SingFanException(String.format("DaoBase.executeWithResult() error: databaseId(%s) can not write !", databaseId));
            }
        }
        else {
            throw new SingFanException(String.format("DaoBase.executeWithResult() error: can not get databaseId from hashKey(%s)",hashKey));
        }
    }

    /**
     * 执行查询的sql语句
     * @param hashKey hashKey
     * @param sql sql语句
     * @param paramList 参数列表
     * @param resultSetReader ResultSet数据读取接口实例
     * @return 查询结果
     */
    protected Object executeQuery(Object hashKey, String sql, List<Object> paramList, IResultSetReader resultSetReader) {
        String databaseId = getDatabaseId(hashKey);
        if (!StringUtil.isNullOrEmpty(databaseId)){
            boolean read = DbContext.getConnectionPoolProvider().read(databaseId);
            if (read){
                Connection connection = null;
                try {
                    connection = getConnection(hashKey);
                    PreparedStatement preparedStatement = createPreparedStatement(connection, sql, paramList);

                    long executeBeginTime = System.currentTimeMillis();
                    ResultSet resultSet = preparedStatement.executeQuery();
                    long executeUsedTime = System.currentTimeMillis() - executeBeginTime;
                    DbLogger.executeSqlLog(executeUsedTime, hashKey, sql, DbUtil.getSqlParamInfo(paramList));

                    Object result = resultSetReader.readObject(resultSet);

                    resultSet.close();
                    preparedStatement.close();
                    return result;
                }
                catch (Exception ex){
                    DbLogger.executeSqlErrorLog(hashKey, sql, DbUtil.getSqlParamInfo(paramList), ex);
                    throw new SingFanException(String.format("DaoBase.executeQuery() error: %1$s hashKey(%2$s) sql(%3$s) params(%4$s) !", ex.getMessage(), hashKey, sql, DbUtil.getSqlParamInfo(paramList)), ex);
                }
                finally {
                    closeConnection(connection);
                }
            }
            else {
                throw new SingFanException(String.format("DaoBase.executeQuery() error: databaseId(%s) can not read !", databaseId));
            }
        }
        else {
            throw new SingFanException(String.format("DaoBase.executeQuery() error: can not get databaseId from hashKey(%s)",hashKey));
        }
    }

    /**
     * 执行查询，并返回查询所返回的结果集中第一行的第一列
     * @param hashKey hashKey
     * @param sql sql语句
     * @param paramList 参数列表
     * @return 第一行的第一列
     */
    protected Object executeScalar(final Object hashKey, final String sql, final List<Object> paramList){
        Object objValue = executeQuery(hashKey, sql, paramList, new IResultSetReader() {
            @Override
            public Object readObject(ResultSet resultSet){
                try {
                    Object result = null;
                    if(resultSet.next()){
                        result = resultSet.getObject(1);
                    }
                    return result;
                }
                catch (Exception ex){
                    throw new SingFanException(String.format("DaoBase.executeScalar().readObject() error: %1$s hashKey(%2$s) sql(%3$s) params(%4$s) !", ex.getMessage(), hashKey, sql, DbUtil.getSqlParamInfo(paramList)), ex);
                }
            }
        });

        return objValue;
    }

    /**
     * 执行查询，并返回查询所返回的结果集中第一行的第一列
     * @param hashKey hashKey
     * @param sql sql语句
     * @param params 参数列表
     * @return 第一行的第一列
     */
    protected Object executeScalar(Object hashKey, String sql, Object... params) {
        List<Object> paramList = DbUtil.params2paramList(params);
        return executeScalar(hashKey, sql, paramList);
    }

    /**
     * 执行查询，并返回查询所返回的结果集中第一行的第一列
     * @param hashKey hashKey
     * @param sql sql语句
     * @return 第一行的第一列
     */
    protected Object executeScalar(Object hashKey, String sql) {
        return executeScalar(hashKey, sql, new ArrayList<>());
    }

    private Connection getConnection(Object hashKey) {
        String databaseId = getDatabaseId(hashKey);
        if (!StringUtil.isNullOrEmpty(databaseId)){
            return DbContext.getConnectionPoolProvider().getConnection(databaseId);
        }
        else {
            throw new SingFanException(String.format("DaoBase.getConnection() error: can not get databaseId from hashKey(%s)",hashKey));
        }
    }

    private static PreparedStatement createPreparedStatement(Connection connection, String sql, List<Object> params) throws SQLException {
        PreparedStatement preparedStatement = connection.prepareStatement(sql);
        fillParams(preparedStatement, params);

        return preparedStatement;
    }

    private static PreparedStatement createPreparedStatementWithReturnGeneratedKeys(Connection connection, String sql, List<Object> params) throws SQLException {
        PreparedStatement preparedStatement = connection.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
        fillParams(preparedStatement, params);

        return preparedStatement;
    }

    private static void fillParams(PreparedStatement preparedStatement, List<Object> params) throws SQLException {
        if(params != null && params.size() > 0){
            int paramIndex = 0;
            for(Object param : params){
                if(param == null ){
                    preparedStatement.setObject(++paramIndex, null);
                }
                else if(param.getClass().getName().equals("java.util.Date")){
                    preparedStatement.setObject(++paramIndex, param, Types.TIMESTAMP);
                }
                else if(param.getClass().getName().equals("java.sql.Date")){
                    preparedStatement.setObject(++paramIndex, param, Types.TIME);
                }
                else{
                    preparedStatement.setObject(++paramIndex, param);
                }
            }
        }
    }

    private static void closeConnection(Connection connection){
        if(connection != null){
            try {
                if(!connection.isClosed()){
                    connection.close();
                }
            }
            catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }
}
